package com.baomidou.springwind.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.springwind.entity.*;
import com.baomidou.springwind.mapper.NetworkLngLatDao;
import com.baomidou.springwind.mapper.NetworkSaleLngLatDao;
import com.baomidou.springwind.mapper.SysAreaDao;
import com.baomidou.springwind.service.NetworkSupplyService;
import com.baomidou.springwind.mapper.NetworkSupplyDao;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.net.URLEncoder;
import java.util.*;

/**
 * excel文件读取
 *
 * @author luox
 */
@Component
public class NetworkSupplyDataReader extends ExcelReader<NetworkSupply> {

    @Autowired
    private NetworkSupplyService networkSupplyService;
    @Autowired
    private NetworkLngLatDao networkLngLatDao;//地区Dao
    @Autowired
    private SysAreaDao sysAreaDao;//地区Dao
    @Autowired
    private NetworkSupplyDao networkSupplyDao;//地区Dao
    @Autowired
    private NetworkSaleLngLatDao networkSaleLngLatDao;//售达方Dao
    @Override
    protected String saveData(List<NetworkSupply> dataList, Long batchId) {
        EntityWrapper<NetworkSupply> wrapper = new EntityWrapper<NetworkSupply>();
        wrapper.eq("batch_id", batchId);
        deleteNetworkSupplyFile(batchId);
        networkSupplyService.delete(wrapper);//删除之前导入的数据
        for (NetworkSupply networkSupply : dataList) {
            networkSupply.setBatchId(batchId);
            networkSupply.setCreateTime(new Date());
            networkSupply.setOperatorId(getCurrentUserId());
        }
        //保存NetworkSupply数据
        networkSupplyService.insertBatch(dataList, 100);
        //一级配送中心经纬度
        List<HashMap<String,Object>> list1=branch(batchId);
        //二级配送中心经纬度
        List<HashMap<String,Object>> list2=second(batchId);
        //匹配所有的一级中心二级中心的经纬度
        List<NetworkSupply> networkSupplyList=networkSupplyService.selectList(wrapper);
        for (int i=0;i<networkSupplyList.size();i++){
            for(int i1=0;i1<list1.size();i1++){
                if (networkSupplyList.get(i).getBranchCenter().equals(list1.get(i1).get("branchCenter"))){
                    networkSupplyList.get(i).setBranchId(Long.valueOf(list1.get(i1).get("id").toString()));
                    networkSupplyList.get(i).setBranchLng(list1.get(i1).get("lng").toString());
                    networkSupplyList.get(i).setBranchLat(list1.get(i1).get("lat").toString());
                    break;
                }
            }
            for (int i2=0;i2<list2.size();i2++){
                if(networkSupplyList.get(i).getDeliveryCenter().equals(list2.get(i2).get("deliveryCenter"))){
                    networkSupplyList.get(i).setDeliveryId(Long.valueOf(list2.get(i2).get("id").toString()));
                    networkSupplyList.get(i).setDeliveryLng(list2.get(i2).get("lng").toString());
                    networkSupplyList.get(i).setDeliveryLat(list2.get(i2).get("lat").toString());
                    break;
                }
            }
        }
        //更新networkSupplyList
        networkSupplyService.updateBatchById(networkSupplyList);
        return "";
    }
    public List<HashMap<String,Object>> second(Long batchId){
        List<HashMap<String,Object>> list2=new ArrayList<HashMap<String, Object>>();
        EntityWrapper<NetworkSupply> wrapper1 = new EntityWrapper<NetworkSupply>();
        wrapper1.eq("batch_id", batchId);
        wrapper1.groupBy("delivery_center");
        List<NetworkSupply> s1=networkSupplyService.selectList(wrapper1);
        String id="";
        String lng="";
        String lat="";
        for (int i=0;i<s1.size();i++){
            //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
            HashMap<String,String> map1=lng2(s1.get(i).getDeliveryCenter());
            //保存经纬度
            HashMap<String, Object> pm = new HashMap<String,Object>();
            pm.put("batchId",batchId);
            pm.put("deliveryCenter",s1.get(i).getDeliveryCenter());
            pm.put("id",map1.get("id"));
            pm.put("lng",map1.get("lng"));
            pm.put("lat",map1.get("lat"));
            list2.add(pm);
            //networkSupplyDao.updateLng2(pm);//备用选择
        }
        return list2;
    }

    public List<HashMap<String,Object>> branch(Long batchId){
        List<HashMap<String,Object>> list1=new ArrayList<HashMap<String, Object>>();
        EntityWrapper<NetworkSupply> wrapper1 = new EntityWrapper<NetworkSupply>();
        wrapper1.eq("batch_id", batchId);
        wrapper1.groupBy("branch_center");
        List<NetworkSupply> s1=networkSupplyService.selectList(wrapper1);
        String id="";
        String lng="";
        String lat="";
        for (int i=0;i<s1.size();i++){
            //保存经纬度
            HashMap<String, Object> pm = new HashMap<String,Object>();
            HashMap<String,String> map1=lng1(s1.get(i).getBranchCenter().replace("工贸",""));
            pm.put("batchId",batchId);
            pm.put("branchCenter",s1.get(i).getBranchCenter());
            pm.put("id",map1.get("id"));
            pm.put("lng",map1.get("lng"));
            pm.put("lat",map1.get("lat"));
            list1.add(pm);
            //networkSupplyDao.updateLng1(pm);//备用选择
        }
        return list1;
    }
    public HashMap<String,String> lng1(String name){
        HashMap<String,String> map=new HashMap<String, String>();
        //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
        EntityWrapper<NetworkLngLat> wrapper2 = new EntityWrapper<NetworkLngLat>();
        wrapper2.eq("name",name);
        List<Map<String, Object>> networkLngLatBranch=networkLngLatDao.selectMaps(wrapper2);
        if (networkLngLatBranch.size()>0){
            map.put("id",networkLngLatBranch.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch.get(0).get("lat").toString());
        }else{
            JSONArray jsonArray=latlng(name);
            NetworkLngLat networkLngLatbran=new NetworkLngLat();
            networkLngLatbran.setName(name);
            networkLngLatbran.setLng(jsonArray.get(0).toString());
            networkLngLatbran.setLat(jsonArray.get(1).toString());
            networkLngLatDao.insert(networkLngLatbran);
            EntityWrapper<NetworkLngLat> wrapperBranch2 = new EntityWrapper<NetworkLngLat>();
            wrapperBranch2.eq("name", name);
            List<Map<String, Object>> networkLngLatBranch2=networkLngLatDao.selectMaps(wrapperBranch2);
            map.put("id",networkLngLatBranch2.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch2.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch2.get(0).get("lat").toString());
        }
        return map;
    }
    public HashMap<String,String> lng2(String name){
        HashMap<String,String> map=new HashMap<String, String>();
        //调用百度地图或者工具来出来地图坐标问题，一级配送中心地图处理
        EntityWrapper<SysArea> wrapperDelivery = new EntityWrapper<SysArea>();
        wrapperDelivery.eq("shortname", name);
        List<Map<String, Object>> s1=sysAreaDao.selectMaps(wrapperDelivery);
        if (s1.size()>0){
            map.put("id",s1.get(0).get("id").toString());
            map.put("lng",s1.get(0).get("lng").toString());
            map.put("lat",s1.get(0).get("lat").toString());
        }else{
            JSONArray jsonArray=latlng(name);
            NetworkLngLat networkLngLatbran=new NetworkLngLat();
            networkLngLatbran.setName(name);
            networkLngLatbran.setLng(jsonArray.get(0).toString());
            networkLngLatbran.setLat(jsonArray.get(1).toString());
            networkLngLatDao.insert(networkLngLatbran);
            EntityWrapper<NetworkLngLat> wrapperBranch2 = new EntityWrapper<NetworkLngLat>();
            wrapperBranch2.eq("name", name);
            List<Map<String, Object>> networkLngLatBranch2=networkLngLatDao.selectMaps(wrapperBranch2);
            map.put("id",networkLngLatBranch2.get(0).get("id").toString());
            map.put("lng",networkLngLatBranch2.get(0).get("lng").toString());
            map.put("lat",networkLngLatBranch2.get(0).get("lat").toString());
        }
        return map;
    }


    private void deleteNetworkSupplyFile(Long batchId) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String path = request.getSession().getServletContext().getRealPath("");
        path = path.substring(0, path.lastIndexOf("\\"));
        path = path.substring(0, path.lastIndexOf("\\"));
        File file = new File(path + "/temp/route/" + batchId + ".obj");
        file.delete();
    }

    @Override
    protected List<NetworkSupply> verifyData(HSSFWorkbook workbook) {
        List<NetworkSupply> networkSupplies = new ArrayList<NetworkSupply>();
        HSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        NetworkSupply networkSupply;
        for (int i = 1; i <= lastRowNum; i++) {
            sheet.getRow(i);
            networkSupply = new NetworkSupply();
            networkSupply.setOrderType(getStringCellValue(sheet.getRow(i), 0));
            networkSupply.setOrderQuantity(getIntCellValue(sheet.getRow(i), 1));
            networkSupply.setSaleDafang(getStringCellValue(sheet.getRow(i), 2));
            /*
            调用百度地图或者工具来处理地图坐标问题：这是售达方经纬度处理
             */
            /*
            EntityWrapper<NetworkSaleLngLat> wrapperSaleLngLat = new EntityWrapper<NetworkSaleLngLat>();
            wrapperSaleLngLat.eq("name", getStringCellValue(sheet.getRow(i), 2));
            List<Map<String, Object>> networkSaleLngLat=networkSaleLngLatDao.selectMaps(wrapperSaleLngLat);
            if (networkSaleLngLat.size()>0){
                networkSupply.setSaleId(Long.valueOf(networkSaleLngLat.get(0).get("id").toString()));
                networkSupply.setSaleLng(networkSaleLngLat.get(0).get("lng").toString());
                networkSupply.setSaleLat(networkSaleLngLat.get(0).get("lat").toString());
            }else{
                JSONArray jsonArray=latlng(getStringCellValue(sheet.getRow(i), 2));
                NetworkSaleLngLat saleLngLat=new NetworkSaleLngLat();
                saleLngLat.setName(getStringCellValue(sheet.getRow(i), 2));
                saleLngLat.setLng(jsonArray.get(0).toString());
                saleLngLat.setLat(jsonArray.get(1).toString());
                networkSaleLngLatDao.insert(saleLngLat);

                EntityWrapper<NetworkSaleLngLat> wrapperSaleLngLat2 = new EntityWrapper<NetworkSaleLngLat>();
                wrapperSaleLngLat2.eq("name", getStringCellValue(sheet.getRow(i), 2));
                List<Map<String, Object>> networkSaleLngLat2=networkSaleLngLatDao.selectMaps(wrapperSaleLngLat2);
                networkSupply.setSaleId(Long.valueOf(networkSaleLngLat2.get(0).get("id").toString()));
                networkSupply.setSaleLng(networkSaleLngLat2.get(0).get("lng").toString());
                networkSupply.setSaleLat(networkSaleLngLat2.get(0).get("lat").toString());
            }*/
            networkSupply.setProductGroup(getStringCellValue(sheet.getRow(i), 3));
            networkSupply.setMaterial(getStringCellValue(sheet.getRow(i), 4));
            networkSupply.setPrimarySales(getStringCellValue(sheet.getRow(i), 5));
            networkSupply.setSecondaryMarketing(getStringCellValue(sheet.getRow(i), 6));
            networkSupply.setBranchCenter(getStringCellValue(sheet.getRow(i), 7));
            networkSupply.setDeliveryCenter(getStringCellValue(sheet.getRow(i), 8));
            networkSupply.setFactoryName(getStringCellValue(sheet.getRow(i), 9));
            networkSupply.setRequestArrival(getDateCellValue(sheet.getRow(i), 10));
            networkSupply.setPromiseArrival(getDateCellValue(sheet.getRow(i), 11));
            networkSupply.setPreparationCar(getDateCellValue(sheet.getRow(i), 12));
            networkSupply.setPickUp(getStringCellValue(sheet.getRow(i), 13));
            networkSupply.setVehicleSchedule(getDateCellValue(sheet.getRow(i), 14));
            networkSupply.setActualArrivalVehicle(getDateCellValue(sheet.getRow(i), 15));
            networkSupply.setNumberVehicles(getIntCellValue(sheet.getRow(i), 16));
            networkSupply.setActualStartoading(getDateCellValue(sheet.getRow(i), 17));
            networkSupply.setActualEndLoading(getDateCellValue(sheet.getRow(i), 18));
            networkSupply.setLoadingQuantity(getIntCellValue(sheet.getRow(i), 19));
            networkSupply.setActualShipment(getDateCellValue(sheet.getRow(i), 20));
            networkSupply.setActualDelivery(getDateCellValue(sheet.getRow(i), 21));
            networkSupply.setExpectedCentre(getDateCellValue(sheet.getRow(i), 22));
            networkSupply.setCarArrive(getDateCellValue(sheet.getRow(i), 23));
            networkSupply.setUnloadingQuantity(getIntCellValue(sheet.getRow(i), 24));
            networkSupply.setStorageGoods(getDateCellValue(sheet.getRow(i), 25));
            networkSupply.setCentresPosting(getDateCellValue(sheet.getRow(i), 26));
            networkSupply.setActualSignature(getDateCellValue(sheet.getRow(i), 27));
            networkSupply.setSignQuantity(getIntCellValue(sheet.getRow(i), 28));
            networkSupply.setReturnQuantity(getIntCellValue(sheet.getRow(i), 29));
            networkSupply.setDelayTransport(getIntCellValue(sheet.getRow(i), 30));
            networkSupply.setDelayArrival(getIntCellValue(sheet.getRow(i), 31));
            networkSupplies.add(networkSupply);
        }
        return networkSupplies;
    }

    @Override
    protected List<NetworkSupply> verifyData7(XSSFWorkbook workbook) {
        return null;
    }


    //通过地址名来获取到经纬度,调用百度地图来处理的
    private JSONArray latlng(String placename){
        placename=placename.replace("工贸","");
        JSONArray jsonArray=new JSONArray();
        placename=placename+"市";
        String url = String.format("http://api.map.baidu.com/geocoder?address=%s&output=json&key=%s", URLEncoder.encode(placename.toString()),"NURx5GUAQ1IBZVBMCQSpYppRsDdTpbxG");
        CloseableHttpClient client = HttpClients.createDefault();
        double lng=0;
        double lat=0;
        try {
            HttpGet httpGet = new HttpGet(url);
            HttpResponse httpResponse = client.execute(httpGet);

            if (httpResponse.getStatusLine().getStatusCode() == 200) {
                HttpEntity httpEntity = httpResponse.getEntity();
                String text = EntityUtils.toString(httpEntity);//取出应答字符串
                JSONObject json = JSONObject.parseObject(text);
                lng = json.getJSONObject("result").getJSONObject("location").getDouble("lng");
                lat = json.getJSONObject("result").getJSONObject("location").getDouble("lat");
                jsonArray.add(lng);
                jsonArray.add(lat);
                return jsonArray;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
